package Dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import JavaBean.UserBean;
import util.SqlHelper;

public class UserDao {
	
	public int insert(UserBean users){
		String  sql="insert into user(username,password,mark) values(?,?,?)";
		String[] parames={users.getUsername(),users.getPassword(),users.getMark()};
		
		int result=SqlHelper.ExecSql(sql, parames);
		if(result>0){
			System.out.println("插入成功！！");
			
		}
		if(result==-1){
			System.out.println("插入失败！！");
		}
		if(result==-2){
			System.out.println("连接错误！！");
		}
		return  result;
		
	}
	//删除用户
	public int delete(int userId){
		String  sql="delete from user where UserId=?";
		int parames=userId;
		
		int result=SqlHelper.ExecSql(sql, parames);
		if(result>0){
			System.out.println("删除成功！！");
			
		}
		if(result==-1){
			System.out.println("删除失败！！");
		}
		if(result==-2){
			System.out.println("连接错误！！");
		}
		return  result;
		
	}
	//修改信息
		public int update(UserBean user){
			String  sql="update user set username=?, mark=? where UserId=?";
			
			String[] params={user.getUsername(),user.getMark(),String.valueOf(user.getUserId())};
			
			int result=SqlHelper.ExecSql(sql, params);
			if(result>0){
				System.out.println("修改成功！！");
				
			}
			if(result==-1){
				System.out.println("删除失败！！");
			}
			if(result==-2){
				System.out.println("连接错误！！");
			}
			return  result;
			
		}
	//查找所有用户信息
	public ArrayList<UserBean> SelectUserInfo(){
		ArrayList<UserBean> user=new ArrayList<UserBean>();
		String  sql="select * from user";
		ResultSet result=SqlHelper.getResultSet(sql);
		try {
			while(result.next()){
				String username=result.getString("username");
				String pwd=result.getString("password");
				String mark=result.getString("mark");
				int UserId=result.getInt("UserId");
				UserBean  bean=new UserBean();
				bean.setMark(mark);
				bean.setPassword(pwd);
				bean.setUsername(username);
				bean.setUserId(UserId);
				user.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return  user;
	}
	//查找所有用户信息
		public ArrayList<UserBean> SelectUserInfoById(int  loginId){
			ArrayList<UserBean> user=new ArrayList<UserBean>();
			String  sql="select * from user where UserId=?";
			int param=loginId;
			ResultSet result=SqlHelper.getResultSet(sql, param);
			try {
				while(result.next()){
					String username=result.getString("username");
					String mark=result.getString("mark");

					UserBean  bean=new UserBean();
					bean.setMark(mark);
					bean.setUsername(username);
					user.add(bean);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return  user;
		}
		//分页查找所有用户信息
				public ArrayList<UserBean> SelectUserInfoPage(int pageSize,int startPos){
					ArrayList<UserBean> user=new ArrayList<UserBean>();
					String sql = "select top "+pageSize+" * from user where UserId not in(select top "+startPos+" UserId from user)";
					ResultSet result=SqlHelper.getResultSet(sql);
					try {
						while(result.next()){
							String username=result.getString("username");
							String mark=result.getString("mark");

							UserBean  bean=new UserBean();
							bean.setMark(mark);
							bean.setUsername(username);
							user.add(bean);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return  user;
				}
}
